CREATE OR REPLACE FUNCTION create_corpus_with_tickets(vCorpusName character varying, vTicketNumbers character varying) RETURNS VOID AS '
DECLARE 

  i INTEGER = 0;
  v_corp_id BIGINT;
  v_lr_id BIGINT;
  v_doc_id BIGINT;
  corpus_lr_type INTEGER;
  p_usr_id INTEGER;
  p_grp_id INTEGER;
  vTicket  CHARACTER VARYING;
  vValueCount INTEGER;
BEGIN

    IF vCorpusName LIKE ''Corpus%'' THEN
        RAISE EXCEPTION ''Corpus names starting with "Corpus" are reserved for corpora processed by the GATE pipeline. Please choose another name.'';
        EXIT;
    END IF;
    
    -- Initialize some ids from catalogs
    SELECT lrtp_id INTO corpus_lr_type FROM t_lr_type WHERE lrtp_type LIKE ''%Corpus%'';
    SELECT grp_id INTO p_grp_id FROM t_group WHERE grp_name LIKE ''USERS'';
    SELECT ugrp_user_id INTO p_usr_id FROM t_user_group WHERE ugrp_group_id = p_grp_id LIMIT 1;

    v_lr_id := NULL;
    v_corp_id := NULL;
    

    -- Create the language resource and corpus if they
    -- do not exist already.
    SELECT r.lr_id INTO v_lr_id FROM t_lang_resource r
        WHERE r.lr_name = vCorpusName;
        
    IF v_lr_id IS NULL THEN

            INSERT into t_lang_resource(lr_id,
                lr_type_id, lr_owner_user_id,
                lr_locking_user_id, lr_owner_group_id,
                lr_name, lr_access_mode,
                lr_parent_id)
            VALUES (nextval(''seq_lang_resource''),
                corpus_lr_type, p_usr_id,
                null, p_grp_id, vCorpusName,
                1, null);

            /* get ID */
            SELECT currval(''seq_lang_resource'')  INTO v_lr_id;

            /* Create the corpus */
            v_corp_id := persist_create_corpus(v_lr_id);
    ELSE
            SELECT corp_id INTO v_corp_id FROM t_corpus WHERE corp_lr_id = v_lr_id;
            IF v_corp_id IS NULL THEN
                v_corp_id := persist_create_corpus(v_lr_id);
            END IF;
    END IF;


    -- Empty the corpus.
    DELETE FROM t_corpus_document 
    WHERE cd_corp_id IN (
        SELECT c.corp_id FROM t_corpus c, t_lang_resource r
        WHERE c.corp_lr_id = r.lr_id AND r.lr_name = vCorpusName);


    vValueCount := char_length(vTicketNumbers) - char_length(replace(vTicketNumbers, '' '', '''')) + 1;
    i := 1;
    LOOP
        vTicket := split_part(vTicketNumbers, '' '', i);
        v_doc_id := NULL;
        SELECT doc_id INTO v_doc_id FROM t_document, t_lang_resource 
        WHERE doc_lr_id = lr_id AND lr_name = vTicket;
        
        IF v_doc_id IS NULL THEN
            RAISE NOTICE ''Document with name % was not found.'', quote_literal(vTicket);
        ELSE
            INSERT INTO t_corpus_document(cd_id, cd_corp_id, cd_doc_id)
            VALUES (nextval(''seq_corpus_document''), v_corp_id, v_doc_id);
        END IF;
        i := i + 1;
        IF i > vValueCount THEN
            EXIT;
        END IF;
    END LOOP;
    RAISE NOTICE ''Corpus with % created'', quote_literal(vCorpusName);
END;
' LANGUAGE 'plpgsql';


-- SELECT create_corpus_with_tickets('TestCorpus', 'ME01001865 ME01000988 ME06002084 ME06002083 ME06024328 ME03017629 ME04010347 ME06024698 ME99002532 ME03012059 ME06002604 ME00010211 ME01000018 ME06001760 ME05020063 ME05003233 ME03003297 ME03017091 ME97000258 ME06005663 ME99001094 ME99000166 ME05003991 ME06001689 ME05003462 ME03011242 ME06008848 ME06015000 ME03004980');